On Thu, 4 Aug 2022 at 13:11, Simon Riggs <simon.riggs@enterprisedb.com> wrote: > > On Wed, 3 Aug 2022 at 20:18, Andres Freund <andres@anarazel.de> wrote: > > > I think we should consider redesigning subtrans more substantially - even with > > the changes you propose here, there's still plenty ways to hit really bad > > performance. And there's only so much we can do about that without more > > fundamental design changes. > > I completely agree - you will be glad to hear that I've been working > on a redesign of the subtrans module. ... > I will post my patch, when complete, in a different thread. The attached patch reduces the overhead of SUBTRANS by minimizing the number of times SubTransSetParent() is called, to below 1% of the current rate in common cases. Instead of blindly calling SubTransSetParent() for every subxid, this proposal only calls SubTransSetParent() when that information will be required for later use. It does this by analyzing all of the callers of SubTransGetParent() and uses these pre-conditions to filter out calls/subxids that will never be required, for various reasons. It redesigns the way XactLockTableWait() calls SubTransGetTopmostTransactionId() to allow this. This short patchset compiles and passes make check-world, with lengthy comments. This might then make viable a simple rewrite of SUBTRANS using a hash table, as proposed by Andres. But in any case, it will allow us to design a densely packed SUBTRANS replacement that does not generate as much contention and I/O. NOTE that this patchset does not touch SUBTRANS at all, it just minimizes the calls in preparation for a later redesign in a later patch. If this patch/later versions of it is committed in Sept CF, then we should be in good shape to post a subtrans redesign patch by major patch deadline at end of year. Patches 001 and 002 are common elements of a different patch, "Smoothing the subtrans performance catastrophe", but other than that, the two patches are otherwise independent of each other. Where does this come from? I learnt a lot about subxids when coding Hot Standby, specifically commit 06da3c570f21394003. This patch just builds upon that earlier understanding. Comments please. -- Simon Riggs http://www.EnterpriseDB.com/